Keyboard shortcuts

Press ← or → to navigate between chapters

Press S or / to search in the book

Press ? to show this help

Press Esc to hide this help

SQL SET Operations

SQL set operations enable you to combine results from multiple queries into a single result set. This script demonstrates the rules and usage of set operations, including UNION, UNION ALL, EXCEPT, and INTERSECT.

Table of Contents

  1. SQL Operation Rules
  2. UNION
  3. UNION ALL
  4. EXCEPT
  5. INTERSECT

1. SQL Operation Rules

1.1 Rule: Data Types – Column Count & Types

Task: For a UNION, the number and data types of columns must match between the two SELECT statements. Write a UNION query combining Sales.Customers and Sales.Employees that (intentionally) violates this rule so you can observe the error in practice.

💡 Suggested Answers 
/* RULE: Data Types
   The data types of columns in each query should match.
*/
SELECT
    FirstName,
    LastName,
    Country
FROM Sales.Customers
UNION
SELECT
    FirstName,
    LastName
FROM Sales.Employees;

1.2 Rule: Data Types – Incompatible Types

Task: Still focusing on the data type rule for set operations, write a UNION query where the first column in the first SELECT is CustomerID and in the second SELECT is FirstName, illustrating a data type mismatch across the two SELECTs.

💡 Suggested Answers 
/* RULE: Data Types (Example)
   The data types of columns in each query should match.
*/
SELECT
    CustomerID,
    LastName
FROM Sales.Customers
UNION
SELECT
    FirstName,
    LastName
FROM Sales.Employees;

1.3 Rule: Column Order

Task: Show a UNION query where the column order differs between the two SELECTs (e.g., LastName, CustomerID vs EmployeeID, LastName) to illustrate why column position matters in set operations.

💡 Suggested Answers 
/* RULE: Column Order
   The order of the columns in each query must be the same.
*/
SELECT
    LastName,
    CustomerID
FROM Sales.Customers
UNION
SELECT
    EmployeeID,
    LastName
FROM Sales.Employees;

1.4 Rule: Column Aliases

Task: Demonstrate that the column names in the result of a set operation are taken from the first SELECT. Write a UNION query that aliases CustomerID as ID and LastName as Last_Name in the first SELECT, while the second SELECT uses plain EmployeeID and LastName.

💡 Suggested Answers 
/* RULE: Column Aliases
   The column names in the result set are determined by the column names
   specified in the first SELECT statement.
*/
SELECT
    CustomerID AS ID,
    LastName AS Last_Name
FROM Sales.Customers
UNION
SELECT
    EmployeeID,
    LastName
FROM Sales.Employees;

1.5 Rule: Correct Columns

Task: Illustrate why it’s important to use the correct matching columns in each SELECT of a set operation. Write a UNION query where the first SELECT is (FirstName, LastName) from Sales.Customers and the second is (LastName, FirstName) from Sales.Employees, showing a semantically wrong but syntactically valid combination.

💡 Suggested Answers 
/* RULE: Correct Columns
   Ensure that the correct columns are used to maintain data consistency.
*/
SELECT
    FirstName,
    LastName
FROM Sales.Customers
UNION
SELECT
    LastName,
    FirstName
FROM Sales.Employees;

2. UNION

2.1 Task 1 – Combine Employees and Customers (without duplicates)

Task: Combine the data from Sales.Customers and Sales.Employees into one table of names, removing duplicates. Return FirstName and LastName from both tables using UNION.

💡 Suggested Answers 
/* TASK 1: 
   Combine the data from Employees and Customers into one table using UNION 
*/
SELECT
    FirstName,
    LastName
FROM Sales.Customers
UNION
SELECT
    FirstName,
    LastName
FROM Sales.Employees;

2.2 Task 5 – Combine Orders and OrdersArchive (without duplicates)

Task: Create a combined orders report from Sales.Orders and Sales.OrdersArchive using UNION so that there are no duplicate rows. Add a column SourceTable identifying whether a row came from 'Orders' or 'OrdersArchive', and order the final result by OrderID.

💡 Suggested Answers 
/* TASK 5: 
   Combine order data from Orders and OrdersArchive into one report without duplicates 
*/
SELECT
    'Orders' AS SourceTable,
    OrderID,
    ProductID,
    CustomerID,
    SalesPersonID,
    OrderDate,
    ShipDate,
    OrderStatus,
    ShipAddress,
    BillAddress,
    Quantity,
    Sales,
    CreationTime
FROM Sales.Orders
UNION
SELECT
    'OrdersArchive' AS SourceTable,
    OrderID,
    ProductID,
    CustomerID,
    SalesPersonID,
    OrderDate,
    ShipDate,
    OrderStatus,
    ShipAddress,
    BillAddress,
    Quantity,
    Sales,
    CreationTime
FROM Sales.OrdersArchive
ORDER BY OrderID;

3. UNION ALL

3.1 Task 2 – Combine Employees and Customers (with duplicates)

Task: Combine the data from Sales.Customers and Sales.Employees into a single result, but this time keep duplicates. Return FirstName and LastName from both tables using UNION ALL.

💡 Suggested Answers 
/* TASK 2: 
   Combine the data from Employees and Customers into one table, including duplicates, using UNION ALL 
*/
SELECT
    FirstName,
    LastName
FROM Sales.Customers
UNION ALL
SELECT
    FirstName,
    LastName
FROM Sales.Employees;

4. EXCEPT

4.1 Task 3 – Employees Who Are NOT Customers

Task: Find all people who appear as employees but not as customers. Use EXCEPT between Sales.Employees and Sales.Customers on FirstName and LastName.

💡 Suggested Answers 
/* TASK 3: 
   Find employees who are NOT customers using EXCEPT 
*/
SELECT
    FirstName,
    LastName
FROM Sales.Employees
EXCEPT
SELECT
    FirstName,
    LastName
FROM Sales.Customers;

5. INTERSECT

5.1 Task 4 – Employees Who Are Also Customers

Task: Find all people who are both employees and customers. Use INTERSECT between Sales.Employees and Sales.Customers on FirstName and LastName.

💡 Suggested Answers 
/* TASK 4: 
   Find employees who are also customers using INTERSECT 
*/
SELECT
    FirstName,
    LastName
FROM Sales.Employees
INTERSECT
SELECT
    FirstName,
    LastName
FROM Sales.Customers;